CREATE TABLE [dbo].[tb_FichaConducta]
(
[idFichaConducta] [int] NOT NULL IDENTITY(1, 1),
[Alumno] [int] NOT NULL,
[CaracteristicaConducta] [int] NOT NULL,
[Valor] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ValorCBO] [int] NULL,
[Observaciones] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FechaRegistro] [datetime] NOT NULL,
[Responsable] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RegistroVigente] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FechaFinVigencia] [datetime] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[TipoDatoConducta] ON [dbo].[tb_FichaConducta] 
FOR INSERT, UPDATE
AS

print 'Este es el Trigger TiposDatosConducta'

declare @valor varchar(20)
declare @tipo varchar(10)

Select @tipo = (Select descripcion from tb_TiposDeValor, tb_Caracteristicas 
		where idCaracteristica = (Select caracteristicaconducta from inserted) and
		idTipoValor = TipoValor)

Select @valor = (Select valor from inserted)

print @tipo
print @valor

if (upper(@tipo) = 'NUMERICO' or upper(@tipo) = 'NUMÉRICO') 
	 if  (isnumeric(@valor) <> 1) begin
		ROLLBACK
		RAISERROR(50011, 16,1)
	end

if (upper(@tipo) = 'FECHA') 
	if  (isdate(@valor) <> 1) begin
		ROLLBACK
		RAISERROR(50011, 16,1)
	end

if (upper(@tipo) = 'SI/NO')
	if (upper(@valor) <> 'SI') 
		if (upper(@valor) <> 'NO') begin
			ROLLBACK
			RAISERROR(50011, 16,1)
		end

		









GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[ValorCBO] ON [dbo].[tb_FichaConducta] 
FOR INSERT, UPDATE

AS

	if ((Select valorCBO from inserted) is not null) and ((Select valor from inserted) is not null) BEGIN
		ROLLBACK
		RAISERROR(50010, 16, 1)
	END
GO
ALTER TABLE [dbo].[tb_FichaConducta] ADD CONSTRAINT [PK_tb_FichaConducta] PRIMARY KEY NONCLUSTERED  ([idFichaConducta]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_FichaConducta] WITH NOCHECK ADD CONSTRAINT [FK_tb_FichaConducta_tb_Caracteristicas] FOREIGN KEY ([CaracteristicaConducta]) REFERENCES [dbo].[tb_Caracteristicas] ([idCaracteristica])
GO
ALTER TABLE [dbo].[tb_FichaConducta] ADD CONSTRAINT [FK_tb_FichaConducta_tb_ValoresCaracteristicas] FOREIGN KEY ([ValorCBO]) REFERENCES [dbo].[tb_ValoresCaracteristicas] ([idValorCaracteristica])
GO
